package com.jplus.core.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import com.jplus.core.utill.Assert;
import com.jplus.core.utill.JSON;
import com.jplus.core.utill.JSON.JSONObject;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/***
 * JDBCTemplate<br>
 * 线程安全的。 2015-12-07 10:48:36
 * 
 * @author yuanqy
 */
public final class JdbcTemplate {
	private String jdbcUrl;
	private String user;
	private String password;
	private Logger log = LoggerFactory.getLogger(JdbcTemplate.class);
	private DataSource datasource;
	private ThreadLocal<Connection> connection = new ThreadLocal<Connection>();
	private ThreadLocal<PreparedStatement> pstmt = new ThreadLocal<PreparedStatement>();
	private ThreadLocal<ResultSet> resultSet = new ThreadLocal<ResultSet>();

	private ThreadLocal<Boolean> TX = new ThreadLocal<Boolean>() {
		@Override
		protected Boolean initialValue() {
			return false; // 默认不开启事务
		}
	};

	public JdbcTemplate(DataSource datasource) {
		this.datasource = datasource;
	}

	public JdbcTemplate(String jdbcUrl, String user, String password) throws ClassNotFoundException {
		init("com.mysql.jdbc.Driver", jdbcUrl, user, password);
	}

	public JdbcTemplate(String driverClass, String jdbcUrl, String user, String password)
			throws ClassNotFoundException {
		init(driverClass, jdbcUrl, user, password);
	}

	private void init(String driverClass, String jdbcUrl, String user, String password) throws ClassNotFoundException {
		try {
			Class.forName(driverClass);
			this.jdbcUrl = jdbcUrl;
			this.user = user;
			this.password = password;
		} catch (Exception e) {
			throw new ClassNotFoundException("找不到驱动程序类 [" + driverClass + "]，加载驱动失败！");
		}
	}

	/**
	 * 增加、删除、改
	 */
	public boolean execute(String sql, Object... params) {
		boolean flag = false;
		try {
			initConnect();
			initParam(sql, params);
			int result = pstmt.get().executeUpdate();
			flag = result > 0 ? true : false;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			destroy();
		}
		return flag;
	}

	/**
	 * 查询单条记录
	 */
	public Map<String, Object> query(String sql, Object... params) {
		List<Map<String, Object>> list = queryList(sql, params);
		Assert.isTrue(list.size() <= 1,
				"Expected one result(or null) to be returned by selectOne(),but found:" + list.size());
		return list.isEmpty() ? null : list.get(0);
	}

	/**
	 * 查询多条记录
	 */
	public List<Map<String, Object>> queryList(String sql, Object... params) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		try {
			initConnect();
			initParam(sql, params);
			resultSet.set(pstmt.get().executeQuery());
			ResultSet rset = resultSet.get();
			ResultSetMetaData metaData = rset.getMetaData();
			while (rset.next())
				list.add(getMap(rset, metaData));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			destroy();
		}
		return list;
	}

	/**
	 * 查询单条记录
	 */
	public <T> T queryBean(String sql, Class<T> cls, Object... params) {
		Map<String, Object> map = query(sql, params);
		if (map == null)
			return null;
		JSONObject json = new JSONObject();
		json.putAll(map);
		return JSON.parseBean(json, cls);
	}

	/**
	 * 查询多条记录
	 */
	public <T> List<T> queryListBean(String sql, Class<T> cls, Object... params) {
		List<T> list = new ArrayList<T>();
		List<Map<String, Object>> result = queryList(sql, params);
		if (!result.isEmpty())
			for (Map<String, Object> map : result) {
				JSONObject json = new JSONObject();
				json.putAll(map);
				list.add(JSON.parseBean(json, cls));
			}
		return list;
	}

	/**
	 * 释放数据库连接
	 */
	public void destroy() {
		if (!TX.get()) {
			if (resultSet.get() != null) { // 关闭记录集
				try {
					resultSet.get().close();
				} catch (SQLException e) {
					log.error("An SQLException occurs when the ResultSet do close()", e);
				} finally {
					resultSet.remove();
				}
			}
			if (pstmt.get() != null) { // 关闭声明
				try {
					pstmt.get().close();
				} catch (SQLException e) {
					log.error("An SQLException occurs when the PreparedStatement do close()", e);
				} finally {
					pstmt.remove();
				}
			}
			if (connection.get() != null) { // 关闭连接对象
				try {
					connection.get().close();
				} catch (SQLException e) {
					log.error("An SQLException occurs when the Connection do close()", e);
				} finally {
					connection.remove();
				}
			}
		}
	}

	/**
	 * 开启事物
	 */
	public void openTransaction() throws SQLException {
		initConnect();
		TX.set(true);
		connection.get().setAutoCommit(false);
	}

	public void openTransaction(int isolationLevel) throws SQLException {
		openTransaction();
		connection.get().setTransactionIsolation(isolationLevel);
	}

	/** 事物回滚 */
	public void rollback() {
		dotx(true);
	}

	/** 事物提交 */
	public void commit() {
		dotx(false);
	}

	private void dotx(boolean bo) {
		if (connection.get() != null) {
			try {
				if (bo)
					connection.get().rollback();
				else
					connection.get().commit();
			} catch (SQLException e) {
				log.error("An SQLException occurs when the Connection do rollback() or commit()", e);
			} finally {
				TX.remove();
				destroy();
			}
		}
	}

	/**
	 * 获得数据库的连接
	 */
	private Connection initConnect() throws SQLException {
		if (connection.get() == null || connection.get().isClosed()) {
			try {
				if (datasource != null) {
					connection.set(datasource.getConnection());
				} else {
					connection.set(DriverManager.getConnection(jdbcUrl, user, password));
				}
			} catch (SQLException e) {
				log.error("JDBC连接失败:", e);
				throw e;
			}
		}
		return connection.get();
	}

	/**
	 * 拼接参数
	 */
	private PreparedStatement initParam(String sql, Object... params) throws SQLException {
		int index = 1;
		pstmt.set(connection.get().prepareStatement(sql));
		if (params != null && params.length >= 0) {
			for (int i = 0; i < params.length; i++) {
				pstmt.get().setObject(index++, params[i]);
			}
		}
		return pstmt.get();
	}

	private Map<String, Object> getMap(ResultSet rset, ResultSetMetaData metaData) throws SQLException {
		Map<String, Object> map = new HashMap<String, Object>();
		int col_len = metaData.getColumnCount();
		for (int i = 0; i < col_len; i++) {
			String cols_name = metaData.getColumnLabel(i + 1);
			map.put(cols_name, rset.getObject(cols_name));
		}
		return map;
	}

}
